package Dao.impl;

import BaseQuery.BaseQuery;
import BaseQuery.AddressQuery;
import Bean.Address;
import Dao.IAddressDao;
import Utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AddressDao implements IAddressDao {

    QueryRunner queryRunner;
    public AddressDao(){
        queryRunner = JdbcUtils.getQueryRunner();
    }


    @Override
    public int add(Address address) throws SQLException {
        String sql = "insert into t_deliveryaddress( contactName, contactSex, contactTel, address, userId) values(?,?,?,?,?)";
        return queryRunner.update(sql, address.getContactName(),
                address.getContactSex(),
                address.getContactTel(),
                address.getAddress(),
                address.getUserId());
    }

    @Override
    public int update(Address address) throws SQLException {
        String sql = "update t_deliveryaddress set contactName=?, contactSex=?, contactTel=?, address=?, userId=? where daId=?";
        return queryRunner.update(sql,address.getContactName(),
                address.getContactSex(),
                address.getContactTel(),
                address.getAddress(),
                address.getUserId(),
                address.getDaId());
    }

    @Override
    public int delete(Integer id) throws SQLException {
        String sql = "delete from t_deliveryaddress where daId=?";
        return queryRunner.update(sql,id);
    }

    @Override
    public Address getById(Integer id) throws SQLException {
        String sql = "select daId,contactName,contactSex,contactTel,address,userId from t_deliveryaddress where daId=?";
        return queryRunner.query(sql,new BeanHandler<Address>(Address.class),id);
    }

    @Override
    public List<Address> getAll() throws SQLException {
        String sql = "select daId,contactName,contactSex,contactTel,address,userId from t_deliveryaddress";
        return queryRunner.query(sql,new BeanListHandler<Address>(Address.class));
    }

    @Override
    public int getCount(BaseQuery baseQuery) throws SQLException {
        AddressQuery addressQuery= (AddressQuery) baseQuery;
        List<Object> params = new ArrayList<>();
        String sql = "select COUNT(daId) from t_deliveryaddress where 1=1 ";
        if(addressQuery.getAddress() != null)
        {
            sql += " and address like concat('%',?,'%')";
            params.add(addressQuery.getAddress());
        }
        System.out.println("查询总记录："+sql);
        System.out.println("参数集合："+params);

        return queryRunner.query(sql,new ScalarHandler<Long>(),params.toArray()).intValue();
    }

    @Override
    public List<Address> getByPage(BaseQuery baseQuery) throws SQLException {
        //传递的真实类型是BusinessQuery  所以强转  目的是获取搜索条件
        AddressQuery query = (AddressQuery)baseQuery;
        //填充?的参数集合  因为sql是动态拼接的  所以?的数量不确定 有需要就加入到集合  不需要则不加入
        List<Object> params = new ArrayList<>();
        //可以动态拼接条件搜索的部分  where 1=1
        String sql = "select daId,contactName,contactSex,contactTel,address,userId from t_deliveryaddress" +
                " where 1=1 ";
        //动态sql  根据条件参数的传递来确定要不要拼接sql
        if (query.getAddress()!=null){
            sql += " and address like concat('%',?,'%')";
            params.add(query.getAddress());
        }
        sql += " ORDER BY daId DESC limit ?,?";
        //给limit后面的两个?填充的  一直都有
        params.add(query.getstartIndex());
        params.add(query.getPageCount());

        System.out.println("查询集合："+sql);
        System.out.println("参数集合："+params);

        return queryRunner.query(sql,new BeanListHandler<Address>(Address.class),params.toArray());
    }
}
